#!/bin/bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
SELECT SUBSTR(transaction_date,1,16),station_id,
sum(case WHEN trim(X.product_code)='92#' THEN 1 ELSE 0 END )AS mumber92,
SUM(CASE WHEN trim(X.product_code)='92#' THEN item_quantity ELSE 0 END) AS LITRE92,
SUM(CASE WHEN trim(X.product_code)='92#' THEN item_value ELSE 0 END)AS MONEY92,
sum(case WHEN trim(X.product_code)='95#' THEN 1 ELSE 0 END )AS mumber95,
SUM(CASE WHEN trim(X.product_code)='95#' THEN item_quantity ELSE 0 END) AS LITRE95,
SUM(CASE WHEN trim(X.product_code)='95#' THEN item_value ELSE 0 END)AS MONEY95,
sum(case WHEN trim(X.product_code)='97#' THEN 1 ELSE 0 END )AS mumber97,
SUM(CASE WHEN trim(X.product_code)='97#' THEN item_quantity ELSE 0 END) AS LITRE97,
SUM(CASE WHEN trim(X.product_code)='97#' THEN item_value ELSE 0 END)AS MONEY97,
sum(case WHEN trim(X.product_code)='0#' THEN 1 ELSE 0 END )AS mumber0,
SUM(CASE WHEN trim(X.product_code)='0#' THEN item_quantity ELSE 0 END) AS LITRE0,
SUM(CASE WHEN trim(X.product_code)='0#' THEN item_value ELSE 0 END)AS MONEY0,
sum(case WHEN trim(X.product_code)='-10#' THEN 1 ELSE 0 END )AS mumber_10,
SUM(CASE WHEN trim(X.product_code)='-10#' THEN item_quantity ELSE 0 END) AS LITRE_10,
SUM(CASE WHEN trim(X.product_code)='-10#' THEN item_value ELSE 0 END)AS MONEY_10,
sum(case WHEN trim(X.product_code)='-20#' THEN 1 ELSE 0 END )AS mumber_20,
SUM(CASE WHEN trim(X.product_code)='-20#' THEN item_quantity ELSE 0 END) AS LITRE_20,
SUM(CASE WHEN trim(X.product_code)='-20#' THEN item_value ELSE 0 END)AS MONEY_20,
sum(case WHEN trim(X.product_code)='98#' THEN 1 ELSE 0 END )AS mumber98,
SUM(CASE WHEN trim(X.product_code)='98#' THEN item_quantity ELSE 0 END) AS LITRE98,
SUM(CASE WHEN trim(X.product_code)='98#' THEN item_value ELSE 0 END)AS MONEY98,
sum(case WHEN trim(X.product_code)='80#' THEN 1 ELSE 0 END )AS mumber80,
SUM(CASE WHEN trim(X.product_code)='80#' THEN item_quantity ELSE 0 END) AS LITRE80,
SUM(CASE WHEN trim(X.product_code)='80#' THEN item_value ELSE 0 END)AS MONEY80,
sum(case WHEN trim(X.product_code)='-35#' THEN 1 ELSE 0 END )AS mumber_35,
SUM(CASE WHEN trim(X.product_code)='-35#' THEN item_quantity ELSE 0 END) AS LITRE_35,
SUM(CASE WHEN trim(X.product_code)='-35#' THEN item_value ELSE 0 END)AS MONEY_35
FROM
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value
from ( SELECT transaction_date,transaction_id,station_id from realtime.transactions where substr(transaction_date,1,10)='$dt' )as Z  join realtime.transaction_items_oil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id where Y.item_type!=20)AS X
group by SUBSTR(transaction_date,1,16),station_id;
EOF